package Operaciones;

import java.sql.*;
import java.util.ArrayList;

import BasedeDatos.*;
import Clases.*;

public class OperacionesBD {
	Connection con;
	
	public boolean existeUsuario (String user, String pass) throws SQLException{
		boolean res = false;
		int a = 0;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM usuario WHERE user = '" + user +"' AND pass = '"+pass+"';";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getInt("guid");
			if(a>0){
				res = true;
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}
	
	public boolean existeSpell (int entry) throws SQLException{
		int a = 0;
		boolean res = false;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM spell WHERE entry =" + entry +";";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getInt("entry");
			if(a == entry){
				res = true;
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}
	
	public boolean existeNpc (int entry) throws SQLException{
		int a = 0;
		boolean res = false;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM npc WHERE entry =" + entry +";";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getInt("entry");
			if(a == entry){
				res = true;
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}
	
	public boolean existeItem (int entry) throws SQLException{
		int a = 0;
		boolean res = false;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM item WHERE entry =" + entry +";";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getInt("entry");
			if(a == entry){
				res = true;
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}
	
	public boolean existeMision (int entryA, int entryH, int ambas) throws SQLException{
		int a = 0;
		int b = 0;
		int c = 0;
		boolean res = false;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM mision WHERE entryA ='" + entryA +"' AND entryH ='"+entryH+"' AND ambas ='"+ambas+"';";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getInt("entryA");
			b = rs.getInt("entryH");
			c = rs.getInt("ambas");
			if(a == entryA || b == entryH || c == ambas){
				res = true;
				System.out.println(res);
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}
	
	public boolean existeUsuarioMail(String mail) throws SQLException{
		boolean res = false;
		String a = "";
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM usuario WHERE email = '" + mail +"';";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getString("email");
			if(a.equals(mail)){
				res = true;
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}
	
	public boolean existeUsuarioUser (String user) throws SQLException{
		boolean res = false;
		int a = 0;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM usuario WHERE user = '" + user +"';";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getInt("guid");
			if(a>0){
				res = true;
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}
	
	public int numeroReporte(int guid) throws SQLException{
		int res = 0;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT COUNT(item) FROM itemasignado WHERE usuario = '" + guid +"' AND reparada ='0';";
		String q2 = "SELECT COUNT(entryA) FROM misionasignado WHERE usuario = '" + guid +"'AND reparada ='0';";
		String q3 = "SELECT COUNT(npc) FROM npcasignado WHERE usuario = '" + guid +"'AND reparada ='0';";
		String q4 = "SELECT COUNT(spell) FROM spellasignado WHERE usuario = '" + guid +"'AND reparada ='0';";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				int a = rs.getInt("count(item)");
				res = res + a;
				}
			rs.close();
			}catch(SQLException e){}
		try{
			ResultSet rs2 = stmt.executeQuery(q2);
			while(rs2.next()){
				int a2 = rs2.getInt("count(entryA)");
				res = res + a2;
				}
			rs2.close();
			}catch(SQLException e){}
		try{
			ResultSet rs3 = stmt.executeQuery(q3);
			while(rs3.next()){
				int a3 = rs3.getInt("count(npc)");
				res = res + a3;
				}
			rs3.close();
			}catch(SQLException e){}
		try{
			ResultSet rs4 = stmt.executeQuery(q4);
			while(rs4.next()){
				int a4 = rs4.getInt("count(spell)");
				res = res + a4;
				}
			rs4.close();
			}catch(SQLException e){}
		return res;
	}
	
	public ArrayList<Npc> selectReporteNpc(int guid) throws SQLException{
		ArrayList<Npc> lista = new ArrayList<Npc>();
		
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT npc FROM npcasignado WHERE usuario = '"+guid+"' AND reparada = '0' ORDER BY prioridad DESC;";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				Npc npc = new Npc();
				npc.setEntry(rs.getInt("npc"));
				System.out.println(npc.getEntry());
				lista.add(npc);
				}
			rs.close();
			}catch(SQLException e){}
		return lista;
	}
	
	public ArrayList<Item> selectReporteItem(int guid) throws SQLException{
		ArrayList<Item> lista = new ArrayList<Item>();
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT item FROM itemasignado WHERE usuario = '"+guid+"' AND reparada = '0' ORDER BY prioridad DESC;";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				Item item = new Item();
				item.setEntry(rs.getInt("item"));
				lista.add(item);
				}
			rs.close();
			}catch(SQLException e){}
		return lista;
	}
	
	public ArrayList<Spell> selectReporteSpell(int guid) throws SQLException{
		ArrayList<Spell> lista = new ArrayList<Spell>();
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT spell FROM spellasignado WHERE usuario = '"+guid+"' AND reparada = '0' ORDER BY prioridad DESC;";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				Spell spell = new Spell();
				spell.setEntry(rs.getInt("spell"));
				lista.add(spell);
				}
			rs.close();
			}catch(SQLException e){}
		return lista;
	}
	
	public ArrayList<Mision> selectReporteMision(int guid) throws SQLException{
		ArrayList<Mision> lista = new ArrayList<Mision>();
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT entryA,entryH,ambas FROM misionasignado WHERE usuario = '"+guid+"' AND reparada = '0' ORDER BY prioridad DESC;";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				Mision mision = new Mision();
				mision.setEntryA(rs.getInt("entryA"));
				mision.setEntryH(rs.getInt("entryH"));
				mision.setAmbas(rs.getInt("ambas"));
				lista.add(mision);
				}
			rs.close();
			}catch(SQLException e){}
		return lista;
	}
	
	public Usuario selectUsuarioGuidNPC(int npc) throws SQLException{
		Usuario u = new Usuario();
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT guid,nuser FROM usuario WHERE guid IN(SELECT usuario FROM npc WHERE entry = '"+npc+"');";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				u.setNUser(rs.getString("nuser"));
				u.setGuid(rs.getInt("guid"));
				}
			rs.close();
			}
			catch(SQLException e){}
		return u;
	}
	
	public Usuario selectUsuarioGuidITEM(int item) throws SQLException{
		Usuario u = new Usuario();
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT guid,nuser FROM usuario WHERE guid IN(SELECT usuario FROM item WHERE entry = '"+item+"');";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				u.setNUser(rs.getString("nuser"));
				u.setGuid(rs.getInt("guid"));
				}
			rs.close();
			}
			catch(SQLException e){}
		return u;
	}
	
	public Usuario selectUsuarioGuidSPELL(int spell) throws SQLException{
		Usuario u = new Usuario();
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT guid,nuser FROM usuario WHERE guid IN(SELECT usuario FROM spell WHERE entry = '"+spell+"');";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				u.setNUser(rs.getString("nuser"));
				u.setGuid(rs.getInt("guid"));
				}
			rs.close();
			}
			catch(SQLException e){}
		return u;
	}
	
	public Usuario selectUsuarioGuidMISION(int entryA,int entryH, int ambas) throws SQLException{
		Usuario u = new Usuario();
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT guid,nuser FROM usuario WHERE guid IN(SELECT usuario FROM mision WHERE entryA = '"+entryA+"' AND entryH = '"+entryH+"' AND ambas = '"+ambas+"');";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				u.setNUser(rs.getString("nuser"));
				u.setGuid(rs.getInt("guid"));
				}
			rs.close();
			}
			catch(SQLException e){}
		return u;
	}
	
	public boolean estaBaneado(String user,String pass) throws SQLException{
		boolean res = false;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT guid FROM baneados WHERE guid IN (SELECT guid FROM usuario WHERE user = '"+user+"' AND pass = '"+pass+"');";
		try{
			ResultSet rs = stmt.executeQuery(q);
			while(rs.next()){
				res = true;
				}
			rs.close();
			}
			catch(SQLException e){}
		return res;
	}
	
	public boolean existeUsuarioNUser (String user) throws SQLException{
		boolean res = false;
		int a = 0;
		con = Conexion.conexion();
		Statement stmt = con.createStatement();
		String q = "SELECT * FROM usuario WHERE nuser = '" + user +"';";
		try{
		ResultSet rs = stmt.executeQuery(q);
		while(rs.next()){
			a = rs.getInt("guid");
			if(a>0){
				res = true;
			}
		}
		rs.close();
		}catch(SQLException e){}
		return res;
	}

}
